﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using CMISLibrary.Libraries;
using BaocaoNPC.Usercontrols.reports;
using BaocaoNPC.common;
using CMISLibrary;
using BCNPCEntities;
using System.Threading;
using System.Data.OleDb;
using System.Windows.Controls;
using System.Data.Odbc;

namespace CMIS2.BaocaoNPC.Usercontrols
{
    /// <summary>
    /// Interaction logic for NPC_HHC.xaml
    /// </summary>
    public partial class NPC_HHC : UserControl
    {
        #region KhaiBaoBien
        /*
        public String strMa_dviqly = UserInfomation.MaDViqly != "" && UserInfomation.MaDViqly != null ? UserInfomation.MaDViqly : "PA04TP";
        public String strTenDNHap = UserInfomation.TenDNHap != "" && UserInfomation.TenDNHap != null ? UserInfomation.TenDNHap : "PD0400";
        public String strCurrentLibID = MenuInfomation.LibID != "" && MenuInfomation.LibID != null ? MenuInfomation.LibID : "65";
        public String strTen_dviqly = UserInfomation.TenDViQLy != "" && UserInfomation.TenDViQLy != null ? UserInfomation.TenDViQLy : "Dien luc Thai Nguyen";
        public String strTen_dviqly_CT = UserInfomation.TenDViQLy_CT != "" && UserInfomation.TenDViQLy_CT != null ? UserInfomation.TenDViQLy_CT : "Cong ty Dien luc Thai Nguyen";
         * */
        public String strMa_dviqly = NPCUS_DanhMuc.Pglobal.strMa_dviqly != "" && NPCUS_DanhMuc.Pglobal.strMa_dviqly != null ? NPCUS_DanhMuc.Pglobal.strMa_dviqly : "PA";
        public String strTenDNHap = NPCUS_DanhMuc.Pglobal.strTen_dangnhap != "" && NPCUS_DanhMuc.Pglobal.strTen_dangnhap != null ? NPCUS_DanhMuc.Pglobal.strTen_dangnhap : "PA";
        public String strTen_dviqly = NPCUS_DanhMuc.Pglobal.strTen_dviqly != "" && NPCUS_DanhMuc.Pglobal.strTen_dviqly != null ? NPCUS_DanhMuc.Pglobal.strTen_dviqly : "cty..";
        public String strTen_dviqly_CT = NPCUS_DanhMuc.Pglobal.strTen_dviqly_CT != "" && NPCUS_DanhMuc.Pglobal.strTen_dviqly_CT != null ? NPCUS_DanhMuc.Pglobal.strTen_dviqly_CT : "cap tren";

        public string tenDBF = "";
        string lfox = "";

        #endregion

        public NPC_HHC()
        {
            InitializeComponent();
            button1.Visibility = Visibility.Hidden;
            button2.Visibility = Visibility.Hidden;
            
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
            string strCommand = "SELECT * FROM meterdata";
            string ts = txtAcc.Text;            

            ts.Replace("\\", "\\\\");
            lbl1.Content = ts;
            OleDbConnection odbConnect = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data source = " + ts);
            OleDbCommand odbCommand = new OleDbCommand(strCommand, odbConnect);
            OleDbDataAdapter odbAdapter = new OleDbDataAdapter(odbCommand);
            DataSet dtResult = new DataSet();
            odbAdapter.Fill(dtResult, "tblUser");
            if (dtResult.Tables["tblUser"].Rows.Count > 0)
            {

                MessageBox.Show("ok");
               
            }
            else
            {
                MessageBox.Show("false");
            }
        }

        private void button2_Click(object sender, RoutedEventArgs e)
        {
            string filepath = "c:\\nn\\";
                
            OdbcConnection CC = new OdbcConnection("Driver={Microsoft dBase VFP Driver (*.dbf)};SourceDB=" + filepath + ";Deleted=Yes;Collate=Machine;Exclusive=No;BackgroundFetch=Yes;SourceType=DBF;Null=Yes;UID=");
            CC.Open();

            OdbcCommand cmd = new OdbcCommand("SELECT * FROM THQTEST1_1311", CC);
            //OdbcDataReader dr = cmd.ExecuteReader();
            OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(cmd);
            DataSet dtResult = new DataSet();
            odbcAdapter.Fill(dtResult, "tbl");


        //     Function LoadData(ByVal str_path As String, ByVal str_TableName As String) As Boolean
        //Dim odbcConn As Odbc.OdbcConnection
        //Dim odbccmd As New Odbc.OdbcCommand
        //Dim da_Fox As New Odbc.OdbcDataAdapter(odbccmd)

        //Dim path As String = str_path
        //Dim str_sql As String

        //odbcConn = New Odbc.OdbcConnection("Driver={Microsoft dBase VFP Driver (*.dbf)};SourceDB=" & path & ";Deleted=Yes;Collate=Machine;Exclusive=No;BackgroundFetch=Yes;SourceType=DBF;Null=Yes;UID=")
        //odbcConn.Open()

        //Try
        //    str_sql = "Select *, 0 AS TYLE from " & str_TableName
        //    With odbccmd

        //        .CommandType = CommandType.Text
        //        .CommandText = str_sql
        //        .Connection = odbcConn
        //    End With



            if (dtResult.Tables["tbl"].Rows.Count > 0)
            {

                MessageBox.Show("ok");

            }
            else
            {
                MessageBox.Show("false");
            }
            CC.Close();
        }

        private void button3_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                bool check = false;

                if (txtAcc.Text.ToString().Trim() == string.Empty)
                {
                    btn_mdb.Focus();
                    check = false;
                }
                else
                {
                    if (txtFox.Text.ToString().Trim() == string.Empty)
                    {
                        btn_hhc.Focus();
                        check = false;
                    }
                    else check = true;
                }


                if (check == true)
                {
                    //=== acc ===

                    string strCommand = "SELECT * FROM meterdata";
                    string lacc = txtAcc.Text.Trim();
                    lacc.Replace("\\", "\\\\");
                    //string tenACC = txtNacc.Text.Trim();
                    //lacc = lacc + "\\" + tenACC + ".mdb";

                    OleDbConnection odbConnect = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data source = " + lacc);
                    OleDbCommand odbCommand = new OleDbCommand(strCommand, odbConnect);
                    OleDbDataAdapter odbAdapter = new OleDbDataAdapter(odbCommand);
                    DataSet dtResult = new DataSet();
                    try
                    {
                        odbAdapter.Fill(dtResult, "acc");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Không thể đọc file  .MDB hoặc sai đường dẫn!");
                    }
                    


                    //=== DBF ===
                    //string filepath = "c:\\nn\\";
                    
                    lfox = lfox + "\\";
                    lfox.Replace("\\", "\\\\");
                    //string tenDBF = txtNfox.Text.Trim();

                    try
                    {
                        OdbcConnection CC = new OdbcConnection("Driver={Microsoft dBase VFP Driver (*.dbf)};SourceDB=" + lfox + ";Deleted=Yes;Collate=Machine;Exclusive=No;BackgroundFetch=Yes;SourceType=DBF;Null=Yes;UID=");
                        string strCommandbc = "select * from " + tenDBF ;
                        OdbcCommand obcCommand = new OdbcCommand(strCommandbc, CC);
                        OdbcDataAdapter obcAdapter = new OdbcDataAdapter(obcCommand);
                        DataSet dsBC = new DataSet();

                        try
                        {
                            obcAdapter.Fill(dsBC, "dbf");
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Không thể đọc file " + tenDBF + ".DBF hoặc sai đường dẫn! ");
                        }



                        CC.Open();

                        string ma_ddo = "";
                        string ma_quyen = "";
                        string ten_khang = "";
                        Decimal cs_moi = 0;
                        int dem = 0;

                        string ma_ddo_dbf = "";
                        string ma_ddo_boP = "";
                        string ma_ddo_boP1 = "";
                        bool demn = false;

                        //=== chuyen so lieu ===
                        for (int i = 0; i < dtResult.Tables["acc"].Rows.Count; i++)
                        {
                            // cac cty dienluc: ma_ddo OMNI + Ma_dviqly, Namdinh de nguyen
                            if (!strMa_dviqly.Contains("PA01"))
                            {
                                string ma_ddo_tam = dtResult.Tables["acc"].Rows[i]["customid"].ToString() + dtResult.Tables["acc"].Rows[i]["meterid"].ToString();
                                ma_ddo = strMa_dviqly + ma_ddo_tam;
                            }
                            else
                            {
                                ma_ddo = dtResult.Tables["acc"].Rows[i]["customid"].ToString() + dtResult.Tables["acc"].Rows[i]["meterid"].ToString();
                            }
                            ma_quyen = dtResult.Tables["acc"].Rows[i]["zone"].ToString();
                            cs_moi = Convert.ToDecimal(dtResult.Tables["acc"].Rows[i]["total"]);
                            ten_khang = dtResult.Tables["acc"].Rows[i]["customname"].ToString();

                            for (int ii = 0; ii < dsBC.Tables["dbf"].Rows.Count; ii++)
                            {
                                ma_ddo_dbf = dsBC.Tables["dbf"].Rows[ii]["ma_ddo"].ToString().Trim();

                                //HHC
                                if (strMa_dviqly.Contains("PA01"))  // Namdinh
                                {
                                    if (ma_ddo_dbf.Length == 12)  // ma_ddo co 12 ky tu -> giữ nguyên
                                    {
                                        ma_ddo_boP = ma_ddo_dbf;
                                    }
                                    else                          // ma_ddo lớn hơn 12 ký tự (thường là 16) -> bỏ P ở đầu
                                    {
                                        ma_ddo_boP1 = ma_ddo_dbf.Remove(0, 1);
                                        ma_ddo_boP = ma_ddo_boP1.Trim();
                                    }
                                }
                                else
                                {
                                    ma_ddo_boP = ma_ddo_dbf;
                                }

                                if (ma_ddo_boP == ma_ddo)
                                {
                                    OdbcCommand cmd = new OdbcCommand("update " + tenDBF + " set cs_moi = " + cs_moi + " where  ma_ddo = '" + ma_ddo_dbf + "'", CC);
                                    cmd.ExecuteNonQuery();
                                    if (cmd.ExecuteNonQuery() > 0)
                                    {
                                        demn = true;
                                        break;
                                    }
                                    else { demn = false; }
 
                                }
        
                            }
                            if (demn == true)
                            {
                                dem = dem + 1;
                                demn = false;
                            }


                            //OdbcCommand cmd = new OdbcCommand("update " + tenDBF + " set cs_moi = " + cs_moi + " where  replace(ma_ddo,'P','') = '" + ma_ddo + "'", CC);
                            //cmd.ExecuteNonQuery();
                            //if (cmd.ExecuteNonQuery() > 0)
                            //{
                            //    dem = dem + 1;
                            //}
                        }

                        CC.Close();

                        MessageBox.Show("Có " + dem.ToString() + " bản ghi được cập nhật ");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Không thể đọc file " + tenDBF + ".DBF hoặc sai đường dẫn! ");
                    }          

                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void btn_mdb_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                System.Windows.Forms.OpenFileDialog a = new System.Windows.Forms.OpenFileDialog();
                a.Filter = "MDB Files (.mdb)|*.mdb|All Files (*.*)|*.*";
                a.FilterIndex = 1;                               
                a.ShowDialog();
                txtAcc.IsReadOnly = true;
                txtAcc.Text = a.FileName;
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void btn_hhc_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                System.Windows.Forms.OpenFileDialog b = new System.Windows.Forms.OpenFileDialog();
                b.Filter = "DBF Files (.dbf)|*.dbf|All Files (*.*)|*.*";
                b.FilterIndex = 1;
                b.ShowDialog();

                txtFox.IsReadOnly = true;
                txtFox.Text = b.FileName;
                string path = b.FileName;
                if(path != "")
                {
                    int t = path.LastIndexOf("\\");
                    lfox = path.Remove(t);
                    string name = b.SafeFileName;
                    int tt = name.LastIndexOf(".");
                    tenDBF = name.Remove(tt);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            
            
        }
    }
}
